package persistencia;
import java.sql.*;
import java.util.ArrayList;

import model.Service;
import model.User;

import persistencia.*;

public class UserMapper {

	private static UserMapper s_userMapper;

	private UserMapper() {
	}

	public static UserMapper GetInstancia() {
		if (s_userMapper == null) {
			s_userMapper = new UserMapper();
		}

		return s_userMapper;
	}

	public void InsertUser(User u) {
		Connection con = PoolConnection.getPoolConnection().getConnection();

		try {
			String senten = "INSERT INTO [User] (dni, nombre, apellido, email, domicilio) VALUES (?,?,?,?,?)";
			PreparedStatement ps = null;
			ps = con.prepareStatement(senten);
			ps.setString(1, u.getDni());
			ps.setString(2, u.getNombre());
			ps.setString(3, u.getApellido());
			ps.setString(4, u.getEmail());
			ps.setString(5, u.getDomicilio());

			ps.execute();

			PoolConnection.getPoolConnection().realeaseConnection(con);
		} catch (SQLException e) {
			System.out.println("Mensaje Error al Insertar Usuario: "
					+ e.getMessage());
			System.out.println("Stack Trace al Insertar Usuario: "
					+ e.getStackTrace());
			PoolConnection.getPoolConnection().realeaseConnection(con);
		}

	}

	public void UpdateUser(User u) {
		// Obtener conexion BD
		Connection con = PoolConnection.getPoolConnection().getConnection();

		// Generar sentencia SQL
		try {
			// Preparar sentencia insert en tabla destino
			String senten = "UPDATE [User] set nombre = ?, apellido = ?, email = ? ,domicilio = ? where dni = ?";
			PreparedStatement ps = null;
			ps = con.prepareStatement(senten);
			ps.setString(1, u.getNombre());
			ps.setString(2, u.getApellido());
			ps.setString(3, u.getEmail());
			ps.setString(4, u.getDomicilio());
			ps.setString(5, u.getDni());

			ps.execute();

		} catch (SQLException e) {
			System.out.println("Mensaje Error al Modificar Usuario: "
					+ e.getMessage());
			System.out.println("Stack Trace al Modificar Usuario: "
					+ e.getStackTrace());
			PoolConnection.getPoolConnection().realeaseConnection(con);
		}
	}

	public void DeleteUser(User u) {

		// Obtener conexion BD
		Connection con = PoolConnection.getPoolConnection().getConnection();

		// Generar sentencia SQL
		try {
			// Preparar sentencia insert en tabla destino
			String senten = "DELETE from [User] where dni = ?";
			PreparedStatement ps = null;
			ps = con.prepareStatement(senten);
			ps.setString(1, u.getDni());
			ps.execute();

			PoolConnection.getPoolConnection().realeaseConnection(con);

		} catch (SQLException e) {
			System.out.println("Mensaje Error al Borrar Usuario: "
					+ e.getMessage());
			System.out.println("Stack Trace al Borrar Usuario: "
					+ e.getStackTrace());
			PoolConnection.getPoolConnection().realeaseConnection(con);
		}

	}

	public User BuscarUser(String dni) {
		User u = null;
		//Obtener conexion BD 
		Connection con = PoolConnection.getPoolConnection().getConnection();
		
		//Generar sentencia SQL
		try
		{
			//Preparar sentencia insert en tabla destino
			String senten = "SELECT nombre, apellido, email, domicilio,dni from [User] where dni = ?" ;
			PreparedStatement ps = null;
			ps = con.prepareStatement(senten);
			ps.setString(1,dni);
			ResultSet result = ps.executeQuery();
			while (result.next())
			{
				u = new User();
				u.populateUser(result.getString("nombre"),
						result.getString("apellido"), result.getString("dni"), result.getString("domicilio"), 
						result.getString("email"));
			}
			
			PoolConnection.getPoolConnection().realeaseConnection(con);
		  }
	      catch( SQLException e ) 
	      {
				System.out.println("Mensaje Error al buscar User: " + e.getMessage());
				System.out.println("Stack Trace al User User: " + e.getStackTrace());
				PoolConnection.getPoolConnection().realeaseConnection(con);
	      }
	      return u;
	}
	
	public ArrayList<User> LoadAllUsers()
	{
		ArrayList<User> ret = new ArrayList<User>();
		Connection con = PoolConnection.getPoolConnection().getConnection();
		User u = null;
		try {
			String senten = "SELECT nombre, apellido, email, domicilio,dni from [User]";
			PreparedStatement ps = null;
			ps = con.prepareStatement(senten);
			
			ResultSet result = ps.executeQuery();
			while (result.next())
			{
				u = new User();
				u.populateUser(result.getString("nombre"),
						result.getString("apellido"), result.getString("dni"), result.getString("domicilio"), 
						result.getString("email"));
				ret.add(u);
			}

			PoolConnection.getPoolConnection().realeaseConnection(con);
		} catch (SQLException e) {
			System.out.println("Mensaje Error al Insertar Usuario: "
					+ e.getMessage());
			System.out.println("Stack Trace al Insertar Usuario: "
					+ e.getStackTrace());
			PoolConnection.getPoolConnection().realeaseConnection(con);
		}
		return ret;
	}

}
